import string, sys, traceback

from yadict.util.exceptionutil import printStack

try:
    from pysqlite2 import dbapi2 as sqlite3    
except:
    from sqlite3 import dbapi2 as sqlite3
         
def openDatabase(pathDatabase):
    
    print("try to load database " + pathDatabase)
    connection = sqlite3.connect(pathDatabase)
    
    return connection

def closeDatabase(connection):
    
    print "close current database"
    
    if connection == None:
        print "no connection"
        return None
    
    connection.close()
    return True

def getTableName(firstChar):
    # verify if table name is nums or a letter
    try:
        ret = int(firstChar)
        # its a number
        return "nums"
    except ValueError:
        return firstChar.lower()

def searchLikeTerms(connection, term, limit):
    
    if connection == None:
        print "no connection"
        return False
    
    if term == None or term.strip() == "":
        print "no valid term"
        return False

    # table name
    tableName = getTableName(term.strip()[0])

    query = "select term from " + tableName + " where lower(term) like '" + term.strip().lower() + "%'"
    if limit > 0:
        query = query + " limit " + str(limit)
    print query
    cursor = connection.cursor()
    cursor.execute(query)
    
    terms = []
    for term in cursor:
        terms.append(term)
        
    cursor.close()
    
    print terms
    return terms

def getTranslate(connection, term):
    
    if connection == None:
        print "no connection"
        return False
    
    if term == None or term.strip() == "":
        print "no valid term"
        return False

    # table name
    tableName = getTableName(term.strip()[0])

    cursor = connection.cursor()
    cursor.execute("select translation from " + tableName + " where lower(term) = ?",(term.lower(),))
    
    term = cursor.fetchone()
    cursor.close()
    
    if term == None:
        return None
    else:
        return term[0]
    
def getTerm(connection, tableName, term):
    
    if connection == None:
        print "no connection"
        return False
    
    if tableName == None or tableName.strip() == "":
        print "no table name"
        return False
    
    cursor = connection.cursor()
    #cursor.execute("select 1 from " + tableName + " where lower(term) = ?", (term.strip().lower().decode('iso-8859-1'),))
    cursor.execute("select 1 from " + tableName + " where lower(term) = ?", (term.strip().lower(),))
    
    if cursor.fetchone() != None:
        cursor.close()
        return True
    else:
        cursor.close()
        return False
    
def checkTableExistence(connection, tableName):
    
    if connection == None:
        print "no connection"
        return False
    
    if tableName == None or tableName.strip() == "":
        print "no table name"
        return False
    
    cursor = connection.cursor()
    cursor.execute("select 1 from sqlite_master where name = ?", (tableName.strip().lower(),))
    if cursor.fetchone() != None:
        cursor.close()
        return True
    else:
        cursor.close()
        return False

def createTable(connection, tableName):
    
    if connection == None:
        print "no connection"
        return False
    
    if tableName == None or tableName.strip() == "":
        print "no table name"
        return False
    
    cursor = connection.cursor()
    cursor.execute('create table ' + tableName + ' (term text, translation text)')
    cursor.close()
    return True

def insertTerm(connection, tableName, term, translation):
    
    if connection == None:
        print "no connection"
        return False
    
    if tableName == None or tableName.strip() == "":
        print "no table name"
        return False
    
    if term == None or term.strip() == "":
        print "no valid term"
        return False

    if translation == None or translation.strip() == "":
        print "no valid translation"
        return False

    if getTerm(connection, tableName, term) == True:
        print "update " + term
        cursor = connection.cursor()
        # it's an update the term already exists   
        #cursor.execute('update ' + tableName + " set translation = translation || ' - ' || ? where lower(term) = ?",(translation.strip().decode('iso-8859-1'),term.strip().lower().decode('iso-8859-1')))
        cursor.execute('update ' + tableName + " set translation = translation || ' - ' || ? where lower(term) = ?",(translation.strip(),term.strip().lower()))
        connection.commit();
        cursor.close()
    else:
        cursor = connection.cursor()
        # it's an insert
        # we have to convert string to 'iso-8859-1'
        #cursor.execute('insert into ' + tableName + ' (term, translation) values (?,?)',  (term.strip().decode('iso-8859-1'), translation.strip().decode('iso-8859-1')))
        cursor.execute('insert into ' + tableName + ' (term, translation) values (?,?)',  (term.strip(), translation.strip()))
        connection.commit();
        cursor.close()
    return True